<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Avoiding table scans of large tables</title>
<link type="text/css" href="../../skin/page.css" rel="stylesheet">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<!--================= start Navigation Path ==================-->
<table summary="navigation path" width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td nowrap="nowrap" valign="middle" bgcolor="#CFDCED" height="20"><img height="1" width="5" alt="" src="../../skin/images/spacer.gif" class="spacer"><!--===== breadcrumb trail (javascript-generated) ====--><font size="2" face="Arial, Helvetica, Sans-serif"><script src="../../skin/breadcrumbs.js" language="JavaScript" type="text/javascript"></script></font></td>
</tr>
<tr>
<td bgcolor="#4C6C8F" height="2"><img height="2" width="2" alt="" src="../../skin/images/spacer.gif" class="spacer"></td>
</tr>
</table>
<!--================= end Navigation Path ==================-->
<!--================= start Banner ==================-->
<table summary="header with logos" width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<!--================= start Group Logo ==================-->
<td bgcolor="#294563"><a href="http://incubator.apache.org"><img border="0" class="logoImage" alt="" src="../../resources/images/apache-incubator.png" title="Derby is a zero admin java based embedded database."></a></td>
<!--================= end Group Logo ==================-->
<!--================= start Project Logo ==================--><td width="100%" align="center" bgcolor="#294563"><a href="http://incubator.apache.org/derby/"><img border="0" class="logoImage" alt="Derby" src="../../images/derby-logo.jpg" title="Derby is a zero admin java based embedded database."></a></td>
<!--================= end Project Logo ==================-->
<!--================= start Search ==================--><td valign="top" rowspan="2" bgcolor="#294563">
<form target="_blank" action="http://www.google.com/search" method="get">
<table summary="search" border="0" cellspacing="0" cellpadding="0" bgcolor="#4C6C8F">
<tr>
<td colspan="3"><img height="10" width="1" alt="" src="../../skin/images/spacer.gif" class="spacer"></td>
</tr>
<tr>
<td><img height="1" width="1" alt="" src="../../skin/images/spacer.gif" class="spacer"></td><td nowrap="nowrap"><input value="incubator.apache.org" name="sitesearch" type="hidden"><input size="15" name="q" id="query" type="text"><img height="1" width="5" alt="" src="../../skin/images/spacer.gif" class="spacer"><input name="Search" value="Search" type="submit">
<br>
<font face="Arial, Helvetica, Sans-serif" size="2" color="white">
                      the Derby site
                      
                      
                    </font></td><td><img height="1" width="1" alt="" src="../../skin/images/spacer.gif" class="spacer"></td>
</tr>
<tr>
<td><img alt="" border="0" height="10" width="9" src="../../skin/images/search-left.gif"></td><td><img height="1" width="1" alt="" src="../../skin/images/spacer.gif" class="spacer"></td><td><img alt="" border="0" height="10" width="9" src="../../skin/images/search-right.gif"></td>
</tr>
</table>
</form>
</td>
<!--================= start Search ==================--><td bgcolor="#294563"><img height="10" width="10" alt="" src="../../skin/images/spacer.gif" class="spacer"></td>
</tr>
<tr>
<td valign="bottom" bgcolor="#294563" colspan="2">
<!--================= start Tabs ==================-->
<div class="tab">
<table summary="tab bar" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="6"><img alt="" height="8" width="6" src="../../skin/images/spacer.gif"></td><td valign="bottom">
<table summary="non selected tab" style="height: 1.6em" border="0" cellpadding="0" cellspacing="0">
<tr>
<td valign="top" width="5" bgcolor="#B2C4E0"><img height="5" width="5" alt="" src="../../skin/images/tab-left.gif"></td><td valign="middle" bgcolor="#B2C4E0"><a class="base-not-selected" href="../../index.html">Home</a></td><td valign="top" width="5" bgcolor="#B2C4E0"><img height="5" width="5" alt="" src="../../skin/images/tab-right.gif"></td>
</tr>
<tr>
<td colspan="3" height="1"></td>
</tr>
</table>
</td><td width="6"><img alt="" height="8" width="6" src="../../skin/images/spacer.gif"></td><td valign="bottom">
<table summary="selected tab" style="height: 1.8em" border="0" cellpadding="0" cellspacing="0">
<tr>
<td valign="top" width="5" bgcolor="#4C6C8F"><img height="5" width="5" alt="" src="../../skin/images/tabSel-left.gif"></td><td valign="middle" bgcolor="#4C6C8F"><font color="#ffffff" size="2" face="Arial, Helvetica, Sans-serif"><b><a class="base-selected" href="../../manuals/index.html">Manuals</a></b></font></td><td valign="top" width="5" bgcolor="#4C6C8F"><img height="5" width="5" alt="" src="../../skin/images/tabSel-right.gif"></td>
</tr>
</table>
</td><td width="6"><img alt="" height="8" width="6" src="../../skin/images/spacer.gif"></td><td valign="bottom">
<table summary="non selected tab" style="height: 1.6em" border="0" cellpadding="0" cellspacing="0">
<tr>
<td valign="top" width="5" bgcolor="#B2C4E0"><img height="5" width="5" alt="" src="../../skin/images/tab-left.gif"></td><td valign="middle" bgcolor="#B2C4E0"><a class="base-not-selected" href="../../papers/index.html">Papers</a></td><td valign="top" width="5" bgcolor="#B2C4E0"><img height="5" width="5" alt="" src="../../skin/images/tab-right.gif"></td>
</tr>
<tr>
<td colspan="3" height="1"></td>
</tr>
</table>
</td>
</tr>
</table>
</div>
<!--================= end Tabs ==================-->
</td><td bgcolor="#294563"><img alt="" width="1" height="1" src="../../skin/images/spacer.gif" class="spacer"></td>
</tr>
<tr>
<td bgcolor="#4C6C8F" colspan="4"><img width="1" height="10" alt="" src="../../skin/images/spacer.gif" class="spacer"></td>
</tr>
</table>
<!--================= end Banner ==================-->
<!--================= start Menu, NavBar, Content ==================-->
<table summary="page content" bgcolor="#ffffff" width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td valign="top">
<table summary="menu" border="0" cellspacing="0" cellpadding="0">
<tr>
<!--================= start left top NavBar ==================-->
<td rowspan="3" valign="top">
<table summary="blue line" border="0" cellpadding="0" cellspacing="0">
<tr>
<td bgcolor="#294563"><img width="10" height="1" alt="" src="../../skin/images/spacer.gif" class="spacer"></td>
</tr>
<tr>
<td bgcolor="#CFDCED"><font color="#4C6C8F" size="4" face="Arial, Helvetica, Sans-serif">&nbsp;</font></td>
</tr>
<tr>
<td bgcolor="#294563"><img width="10" height="1" alt="" src="../../skin/images/spacer.gif" class="spacer"></td>
</tr>
</table>
</td>
<!--================= end left top NavBar ==================--><td bgcolor="#294563"><img width="1" height="1" alt="" src="../../skin/images/spacer.gif" class="spacer"></td><td valign="bottom" bgcolor="#4C6C8F"><img width="10" height="10" alt="" src="../../skin/images/spacer.gif" class="spacer"></td><td nowrap="nowrap" valign="top" bgcolor="#4C6C8F">
<!--================= start Menu items ==================-->
<div class="menu">
<ul>
<li>
<font color="#CFDCED">Manuals</font>
<ul>
     
<li>
<a href="../../manuals/index.html">About</a>
</li>
     
<li>
<font color="#CFDCED">Getting Started</font>
<ul>
         
<li>
<a href="../../manuals/getstart/gspr02.html" title="Table of Contents">Table of Contents</a>
</li>
         
<li>
<a href="../../manuals/getstart/gspr40.html" title="Index">Index</a>
</li>
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
     
</ul>
</li>
     
<li>
<font color="#CFDCED">Reference Manual</font>
<ul>
         
<li>
<a href="../../manuals/reference/sqlj02.html" title="Table of Contents">Table of Contents</a>
</li>
         
<li>
<a href="../../manuals/reference/sqlj275.html" title="Index">Index</a>
</li>
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
     
</ul>
</li>

     
<li>
<font color="#CFDCED">Developer's Guide</font>
<ul>
         
<li>
<a href="../../manuals/develop/develop02.html" title="Table of Contents">Table of Contents</a>
</li>
         
<li>
<a href="../../manuals/develop/develop157.html" title="Index">Index</a>
</li>
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
     
</ul>
</li>
     
<li>
<font color="#CFDCED">Tuning Derby</font>
<ul>
         
<li>
<a href="../../manuals/tuning/perf02.html" title="Table of Contents">Table of Contents</a>
</li>
         
<li>
<a href="../../manuals/tuning/perf121.html" title="Index">Index</a>
</li>
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
     
</ul>
</li>

     
<li>
<font color="#CFDCED">Server &amp; Admin Guide</font>
<ul>
         
<li>
<a href="../../manuals/admin/hubprnt02.html" title="Table of Contents">Table of Contents</a>
</li>
         
<li>
<a href="../../manuals/admin/hubprnt65.html" title="Index">Index</a>
</li>
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
        
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
    
</ul>
</li>

    
<li>
<font color="#CFDCED">Tools &amp; Utility Guide</font>
<ul>
         
<li>
<a href="../../manuals/tools/tools02.html" title="Table of Contents">Table of Contents</a>
</li>
         
<li>
<a href="../../manuals/tools/tools113.html" title="Index">Index</a>
</li>
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
         
    
</ul>
</li>
  
</ul>
</li>
</ul>
</div>
<!--================= end Menu items ==================-->
</td><td valign="bottom" bgcolor="#4C6C8F"><img width="10" height="10" alt="" src="../../skin/images/spacer.gif" class="spacer"></td><td bgcolor="#294563"><img width="1" height="1" alt="" src="../../skin/images/spacer.gif" class="spacer"></td>
</tr>
<tr>
<td valign="bottom" align="left" colspan="2" rowspan="2" bgcolor="#4C6C8F"><img height="10" width="10" border="0" alt="" src="../../skin/images/menu-left.gif"></td><td bgcolor="#4C6C8F"><img height="10" width="10" alt="" src="../../skin/images/spacer.gif" class="spacer"></td><td valign="bottom" align="right" colspan="2" rowspan="2" bgcolor="#4C6C8F"><img height="10" width="10" border="0" alt="" src="../../skin/images/menu-right.gif"></td>
</tr>
<tr>
<td height="1" bgcolor="#294563"><img width="1" height="1" alt="" src="../../skin/images/spacer.gif" class="spacer"></td>
</tr>
</table>
</td><td valign="top" width="100%">
<table summary="content" width="100%" border="0" cellpadding="0" cellspacing="0">
<!--================= start middle NavBar ==================-->
<tr>
<td colspan="4" bgcolor="#294563"><img width="10" height="1" alt="" src="../../skin/images/spacer.gif" class="spacer"></td>
</tr>
<tr>
<td align="left" width="10" bgcolor="#CFDCED"><img width="10" height="1" alt="" src="../../skin/images/spacer.gif" class="spacer"></td><td align="left" width="50%" bgcolor="#CFDCED"><font color="#4C6C8F" size="3" face="Arial, Helvetica, Sans-serif">
                &nbsp;
                
                </font><img width="10" height="8" alt="" src="../../skin/images/spacer.gif" class="spacer"></td><td align="right" width="50%" bgcolor="#CFDCED"><font color="#4C6C8F" size="3" face="Arial, Helvetica, Sans-serif">
                &nbsp;
                
                </font><img width="10" height="8" alt="" src="../../skin/images/spacer.gif" class="spacer"></td><td width="10" bgcolor="#CFDCED"><img width="10" height="1" alt="" src="../../skin/images/spacer.gif" class="spacer"></td>
</tr>
<tr>
<td colspan="4" bgcolor="#294563"><img width="10" height="1" alt="" src="../../skin/images/spacer.gif" class="spacer"></td>
</tr>
<!--================= end middle NavBar ==================-->
<!--================= start Content==================-->
<tr>
<td align="left" width="10"><img width="10" height="1" alt="" src="../../skin/images/spacer.gif" class="spacer"></td><td colspan="2" align="left" width="100%">
<div class="content">
<table class="title" summary="">
<tr>
<td valign="middle">
<h1>Avoiding table scans of large tables</h1>
</td>
</tr>
</table>
<ul class="minitoc">
<li>
<a href="#Avoiding+table+scans+of+large+tables">Avoiding table scans of large tables</a>
</li>
<li>
<a href="#Index%2C+Index%2C+Index">Index, Index, Index</a>
</li>
<li>
<a href="#Create+useful+indexes">Create useful indexes</a>
</li>
<li>
<a href="#Make+sure+indexes+are+being+used%2C+and+rebuild+them">Make sure indexes are being used, and rebuild them</a>
</li>
<li>
<a href="#Think+about+index+order">Think about index order</a>
</li>
<li>
<a href="#Think+About+Join+Order">Think About Join Order</a>
</li>
<li>
<a href="#Decide+Whether+a+Descending+Index+Would+Be+Useful">Decide Whether a Descending Index Would Be Useful</a>
</li>
<li>
<a href="#Prevent+the+user+from+issuing+expensive+queries">Prevent the user from issuing expensive queries</a>
</li>
</ul>
<a name="N1003F"></a><a name="Avoiding+table+scans+of+large+tables"></a>
<h3>Avoiding table scans of large tables</h3>
<div style="margin-left: 0 ; border: 2px">
<p>Derby is fast and efficient, but when tables are huge, scanning tables might take longer than a user would expect. It's even worse if you then ask Derby to sort this data.</p>
<p>Things that you can do to avoid table scans fall into two categories. These categories are, in order of importance:</p>
<ol>
<li>Index, Index, Index</li>
<li>Prevent the user from issuing expensive queries</li>
</ol>
</div>
<a name="N1004C"></a><a name="Index%2C+Index%2C+Index"></a>
<h3>Index, Index, Index</h3>
<div style="margin-left: 0 ; border: 2px">
<p>Have you ever thought what it would be like to look up a phone number in the phone book of a major metropolitan city if the book were not indexed by name? For example, to look up the phone number for John Jones, you could not go straight to the <em>J</em> page. You would have to read the entire book. That is what a table scan is like. Derby has to read the entire table to retrieve what you are looking for unless you create useful indexes on your table.</p>
</div>
<a name="N10055"></a><a name="Create+useful+indexes"></a>
<h3>Create useful indexes</h3>
<div style="margin-left: 0 ; border: 2px">
<p>Indexes are useful when a query contains a WHERE clause. Without a WHERE clause, Derby is <em>supposed</em> to return all the data in the table, and so a table scan is the correct (if not desirable) behavior. (More about that in <a href="#HDRSII-DEPTH-36205">Prevent the user from issuing expensive queries</a>.)</p>
<p>Derby creates indexes on tables in the following situations:</p>
<ul>
<li>When you define a primary key, unique, or foreign key constraint on a table. See "CONSTRAINT clause" in the <cite>Derby Reference Manual</cite> for more information.</li>
<li>When you explicitly create an index on a table with a CREATE INDEX statement.</li>
</ul>
<p>For an index to be useful for a particular statement, one of the columns in the statement's WHERE clause must be the first column in the index's key.</p>
<dl>
<dt>
<strong>Note:</strong>
</dt>
<dd>For a complete discussion of how indexes work and when they are useful (including pictures), see <a href="perf43.html#HDRSII-OPTIMZ-33368">What Is an Index?</a> and <a href="perf43.html#HDRSII-OPTIMZ-30217">Index use and access paths</a>.</dd>
</dl>
<p>Indexes provide some other benefits as well:</p>
<ul>
<li>If all the data requested are in the index, Derby does not have to go to the table at all. (See <a href="perf43.html#HDRSII-OPTIMZ-30768">Covering Indexes</a>.)</li>
<li>For operations that require a sort (ORDER BY), if Derby uses the index to retrieve the data, it does not have to perform a separate sorting step for some of these operations in some situations. (See <a href="perf45.html#HDRSII-OPTIMZ-27036">About the Optimizer's Choice of Sort Avoidance</a>.)</li>
</ul>
<dl>
<dt>
<strong>Note:</strong>
</dt>
<dd>Derby does not support indexing long columns.</dd>
</dl>
</div>
<a name="N10091"></a><a name="Make+sure+indexes+are+being+used%2C+and+rebuild+them"></a>
<h3>Make sure indexes are being used, and rebuild them</h3>
<div style="margin-left: 0 ; border: 2px">
<p>If an index is useful for a query, Derby is probably using it. However, you need to make sure. Analyze the way Derby is executing your application's queries. See <a href="perf36.html#HDRSII-DEPTH-33391">Analyzing Statement Execution</a> for information on how to do this.</p>
<p>In addition, over time, index pages fragment. Rebuilding indexes improves performance significantly in these situations. To rebuild an index, drop it and then re-create it.</p>
</div>
<a name="N1009E"></a><a name="Think+about+index+order"></a>
<h3>Think about index order</h3>
<div style="margin-left: 0 ; border: 2px">
<p>
<a name="IDX431"></a>  Derby allows you to create index columns in descending order in addition to creating them in ascending order, the default. Descending indexes provide performance benefits for the following kinds of queries:</p>
<ul>
<li>Queries that require sorting data in descending order.</li>
<li>Queries that return the minimum or maximum value.</li>
</ul>
<p>Such indexes can also be used in the same way that ascending indexes are.</p>
<p>To ensure performance benefits, verify that the descending index is being used. Force the index if necessary. See <a href="perf36.html#HDRSII-DEPTH-33391">Analyzing Statement Execution</a> for information on how to do this.</p>
</div>
<a name="N100B3"></a><a name="Think+About+Join+Order"></a>
<h3>Think About Join Order</h3>
<div style="margin-left: 0 ; border: 2px">
<p>For some queries, join order can make the difference between a table scan (expensive) and an index scan (cheap). Here's an example:</p>
<pre>
<strong>select fts.flight_id, fa.flight_date, fts.depart_time
from flights fts, flightavailability fa
where fts.flight_id = fa.flight_id and 
fts.segment_number = fa.segment_number
and fts.dest_airport = 'CDG'
and fts.orig_airport = 'DUB'
order by flight_date</strong>

</pre>
<p>If Derby chooses <em>Hotels</em> as the outer table, it can use the index on <em>Hotels</em> to retrieve qualifying rows. (Given the data in <em>toursDB</em>, it will return three rows; three hotels have a <em>city_id</em> of 10.) Then it need only look up data in <em>HotelAvailability</em> three times, once for each qualifying row. And to retrieve the appropriate rows from <em>HotelAvailability</em>, it can use an index for <em>HotelAvailability</em>'s <em>hotel_id</em> column instead of scanning the entire table.</p>
<p>If Derby chooses the other order, with <em>HotelAvailability</em> as the outer table, it will have to probe the <em>Hotels</em> table for <em>every row</em>, not just three rows, because there are no qualifications on the <em>HotelAvailability</em> table.</p>
<p>For more information about join order, see <a href="perf44.html#HDRSII-OPTIMZ-12168">Joins and Performance</a>.</p>
<p>Derby usually chooses a good join order. However, as with index use, you should make sure. Analyze the way Derby is executing your application's queries. See <a href="perf36.html#HDRSII-DEPTH-33391">Analyzing Statement Execution</a> for information on how to do this.</p>
</div>
<a name="N100F2"></a><a name="Decide+Whether+a+Descending+Index+Would+Be+Useful"></a>
<h3>Decide Whether a Descending Index Would Be Useful</h3>
<div style="margin-left: 0 ; border: 2px">
<p>Derby allows you to create an index that uses the descending order for a column. Such indexes improve the performance of queries that order results in descending order or that search for the minimum or maximum value of an indexed column. For example, both of the following queries could benefit from indexes that use descending ordering:</p>
<pre>
<strong>-- would benefit from an index like this:
-- CREATE INDEX c_id_desc ON Citites(city_id DESC) 
SELECT * FROM Cities ORDER BY citiy_id DESC
 -- would benefit from an index like this:
-- CREATE INDEX f_miles_desc on Flights(miles DESC) 
SELECT MAX(miles) FROM Flight
 -- would benefit from an index like this:
-- CREATE INDEX arrival_time_desc ON Flights(dest_airport, arrive_time DESC)
SELECT * FROM Flights WHERE dest_airport = 'LAX' 
ORDER BY ARRIVAL DESC</strong>

</pre>
</div>
<a name="N100FD"></a><a name="Prevent+the+user+from+issuing+expensive+queries"></a>
<h3>Prevent the user from issuing expensive queries</h3>
<div style="margin-left: 0 ; border: 2px">
<p>Some applications have complete control over the queries that they issue; the queries are built into the applications. Other applications allow users to construct queries by filling in fields on a form. Any time you let users construct ad-hoc queries, you risk the possibility that the query a user constructs will be one like the following:</p>
<pre>
<strong>SELECT * FROM ExtremelyHugeTable
ORDER BY unIndexedColumn</strong>

</pre>
<p>This statement has no WHERE clause. It will require a full table scan. To make matters worse, Derby will then have to order the data. Most likely, the user does not want to browse through all 100,000 rows, and does not care whether the rows are all in order.</p>
<p>Do everything you can to avoid table scans and sorting of large results (such as table scans).</p>
<p>
<a name="IDX436"></a> Some things you can do to disallow such runaway queries:</p>
<ul>
<li>Use client-side checking to make sure some minimal fields are always filled in. Eliminate or disallow queries that cannot use indexes and are not optimizable. In other words, force an optimizable WHERE clause by making sure that the columns on which an index is built are included in the WHERE clause of the query. Reduce or disallow DISTINCT clauses (which often require sorting) on large tables.</li>
<li>For queries with large results, do not let the database do the ordering. Retrieve data in chunks (provide a Next button to allow the user to retrieve the next chunk, if desired), and order the data in the application.</li>
<li>Do not use SELECT DISTINCT to populate lists; instead, maintain a separate table of the unique items.</li>
</ul>
<hr>
<a href="perf32.html">Previous Page</a>
<br>
<a href="perf34.html">Next Page</a>
<br>
<a href="perf02.html#ToC">Table of Contents</a>
<br>
<a href="perf121.html#HDRINDEX_START">Index</a>
</div>
<div class="attribution"></div>
</div>
</td><td width="10"><img width="10" height="1" alt="" src="../../skin/images/spacer.gif" class="spacer"></td>
</tr>
<!--================= end Content==================-->
</table>
</td>
</tr>
</table>
<!--================= end Menu, NavBar, Content ==================-->
<!--================= start Footer ==================-->
<table summary="footer" cellspacing="0" cellpadding="0" width="100%" border="0">
<tr>
<td colspan="2" height="1" bgcolor="#4C6C8F"><img height="1" width="1" alt="" src="../../skin/images/spacer.gif" class="spacer"><a href="../../skin/images/label.gif"></a><a href="../../skin/images/page.gif"></a><a href="../../skin/images/chapter.gif"></a><a href="../../skin/images/chapter_open.gif"></a><a href="../../skin/images/current.gif"></a><a href="/favicon.ico"></a></td>
</tr>
<tr>
<td colspan="2" bgcolor="#CFDCED" class="copyright" align="center"><font size="2" face="Arial, Helvetica, Sans-Serif">Copyright &copy;
          2004&nbsp;Apache Software Foundation All rights reserved.<script type="text/javascript" language="JavaScript"><!--
              document.write(" - "+"Last Published: " + document.lastModified);
            //  --></script></font></td>
</tr>
<tr>
<td colspan="2" align="left" bgcolor="#CFDCED" class="logos"></td>
</tr>
</table>
<!--================= end Footer ==================-->
</body>
</html>
